<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Select Reviewer</title>
</head>
<body>
<p>
	<%-- Import the java.sql package --%>
	<%@ page import="java.sql.*"%>
	<%-- -------- Open Connection Code -------- --%>
	<%
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			// Registering Postgresql JDBC driver with the DriverManager
			Class.forName("org.postgresql.Driver");

			// Open a connection to the database using DriverManager
			conn = DriverManager
					.getConnection("jdbc:postgresql://localhost/admissions?"
							+ "user=postgres&password=27428572");
	%>
	<%
		// Begin transaction
			//insert Address
			%>
			<form action="/admission/protected/reviewer/index.jsp" method="GET"">
			<SELECT NAME="selectedReviewer">
			<% 
			conn.setAutoCommit(false);
			Statement stmt = conn.createStatement();
			Statement stmt2 = conn.createStatement();
			rs = stmt.executeQuery("SELECT user_name FROM user_roles WHERE role='reviewer'");
			if(rs!= null)
				while(rs.next())
				{
					String reviewer = rs.getString(1);
					%>
					<%=session.getAttribute("appId")%>
					<% 
					
					ResultSet rs2 = stmt2.executeQuery("SELECT id FROM list_application WHERE student_id='"+session.getAttribute("appId").toString()+
							"' AND reviewer_name='"+rs.getString(1)+"'");
					if(rs2!=null)
					{
					if(!rs2.next())
					{
					%>
					<option value="<%=rs.getString(1)%>"><%=rs.getString(1)%></option>
					<%
					}
					}
				}
			%>
			</SELECT>
			<p>
			<INPUT TYPE="SUBMIT" VALUE="Submit"/>
			</p>
			</form>
			<%
			conn.setAutoCommit(true);
			
		  }

		catch (SQLException e){
			throw new RuntimeException(e);
		}

		finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
				}
				rs = null;
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
				}
				pstmt = null;
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
				conn = null;
			}

		}
		%>

</body>
</html>